package com.sql.connection;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.tomcat.jdbc.pool.DataSource;
import org.apache.tomcat.jdbc.pool.PoolProperties;

public class ConnectionPoolByDataSource {

	/**
	 * Overview about Connection Pool: http://www.javaranch.com/journal/200601/JDBCConnectionPooling.html
	 * 
	 * Here is Connection Pool using DataSouce of Apache Tomcat.
	 * Apache Tomcat will handle connection pool.
	 * If use Tomcat for deploy application, not need to use more lib. If not, use 2 lib: tomcat-jdbc.jar && tomcat-juli.jar
	 * @param args
	 * @throws SQLException
	 * @throws InterruptedException
	 */
	
	/* ###########################
	 * Another way is use Resource for JNDI lookups  like: 
	 * http://tomcat.apache.org/tomcat-7.0-doc/jndi-datasource-examples-howto.html
	 * http://docs.jboss.org/jbossweb/3.0.x/jndi-resources-howto.html
	 * ###########################
	 	<Context> //From server.xml in Server
	 	...
		 	<Resource name="jdbc/TestDB"
	          auth="Container"
	          type="javax.sql.DataSource"
	          factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
	          testWhileIdle="true"
	          testOnBorrow="true"
	          testOnReturn="false"
	          validationQuery="SELECT 1"
	          validationInterval="30000"
	          timeBetweenEvictionRunsMillis="30000"
	          maxActive="100"
	          minIdle="10"
	          maxWait="10000"
	          initialSize="10"
	          removeAbandonedTimeout="60"
	          removeAbandoned="true"
	          logAbandoned="true"
	          minEvictableIdleTimeMillis="30000"
	          jmxEnabled="true"
	          jdbcInterceptors="org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;
	            org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer"
	          username="root"
	          password="password"
	          driverClassName="com.mysql.jdbc.Driver"
	          url="jdbc:mysql://localhost:3306/mysql"/>
	    </Context>
        ###########################################
        #Java Code:#
        Context initCtx = new InitialContext();
		Context envCtx = (Context) initCtx.lookup("java:comp/env");

		// Look up our data source
		DataSource ds = (DataSource)envCtx.lookup("jdbc/TestDB");

		// Allocate and use a connection from the pool
		Connection conn = ds.getConnection();
		//... use this connection to access the database ...
		conn.close();
	 */
	public static void main(String[] args) throws SQLException, InterruptedException {
		PoolProperties p = new PoolProperties();
        p.setUrl("jdbc:sqlserver://192.168.71.188\\Sinarmas:1433;databaseName=SINARMAS_LSMK");
        p.setDriverClassName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        p.setUsername("sa");
        p.setPassword("sa");
        p.setJmxEnabled(true);
        p.setTestWhileIdle(false);
        p.setTestOnBorrow(true);
        p.setValidationQuery("SELECT 1");
        p.setTestOnReturn(false);
        p.setValidationInterval(30000);
        p.setTimeBetweenEvictionRunsMillis(30000);
        p.setMaxActive(100);
        p.setInitialSize(10);
        p.setMaxWait(10000);
        p.setRemoveAbandonedTimeout(60);
        p.setMinEvictableIdleTimeMillis(30000);
        p.setMinIdle(10);
        p.setLogAbandoned(true);
        p.setRemoveAbandoned(true);
        p.setJdbcInterceptors(
          "org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;"+
          "org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer");
        DataSource datasource = new DataSource();
        datasource.setPoolProperties(p);

        Connection con = null;
        try {
          con = datasource.getConnection();
          Statement st = con.createStatement();
          ResultSet rs = st.executeQuery("select * from starworks.XbrlOutput");
          while (rs.next()) {
              for(int i = 1; i < rs.getMetaData().getColumnCount(); i++) {
            	  System.out.print(rs.getString(i) + " -- ");
              }
              System.out.println();
          }
          rs.close();
          st.close();
          Thread.sleep(5000);
        } finally {
          if (con!=null) try {con.close();}catch (Exception ignore) {}
        }
	}

}
